import pymysql
import csv
import os
import sys
import re

print("===== 简化版产品导入工具 =====")
print(f"当前工作目录: {os.getcwd()}")

# 检查CSV文件
csv_path = 'backend/data/products.csv'
if not os.path.exists(csv_path):
    print(f"错误：找不到CSV文件 {csv_path}")
    sys.exit(1)

print(f"CSV文件存在，大小：{os.path.getsize(csv_path)/1024:.2f} KB")

# 连接数据库
try:
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='premade_food_trace',
        charset='utf8mb4'
    )
    print("数据库连接成功")
except Exception as e:
    print(f"数据库连接失败: {e}")
    sys.exit(1)

# 定义正则表达式检查 code 格式
code_pattern = re.compile(r'^SP-\d{8}-\d{3}$')

try:
    with connection.cursor() as cursor:
        # 获取有效的supplier_id
        cursor.execute("SELECT id FROM suppliers")
        supplier_records = cursor.fetchall()
        valid_supplier_ids = {row[0] for row in supplier_records}
        print(f"发现 {len(valid_supplier_ids)} 个有效供应商ID")

        # 如果没有供应商，直接退出
        if not valid_supplier_ids:
            print("警告：没有供应商ID，无法导入产品")
            sys.exit(1)
            
        # 查看数据库表结构
        cursor.execute("DESCRIBE products")
        columns_info = cursor.fetchall()
        print("\n===== 数据库表结构 =====")
        db_columns = []
        for col in columns_info:
            db_columns.append(col[0])
            print(f"字段: {col[0]}, 类型: {col[1]}")
        
        print(f"\n表字段: {', '.join(db_columns)}")

        # 读取CSV文件
        total = 0
        imported = 0
        skipped = 0

        # 尝试GBK编码读取
        try:
            with open(csv_path, 'r', encoding='gbk') as csvfile:
                reader = csv.DictReader(csvfile)
                
                # 输出CSV文件的列名
                print(f"\nCSV文件列名: {', '.join(reader.fieldnames)}")
                
                for row in reader:
                    total += 1
                    
                    # 验证 code 格式
                    if 'code' not in row or not row['code'] or not code_pattern.match(row['code']):
                        code_value = row.get('code', '缺失')
                        if total <= 5:
                            print(f"跳过无效的产品编码: {code_value}")
                        skipped += 1
                        continue
                    
                    # 检查supplier_id是否有效
                    try:
                        supplier_id = int(row.get('supplier_id', 0))
                        if supplier_id in valid_supplier_ids:
                            # 确保所有必要字段都有值
                            for field in ['name', 'category', 'shelf_life', 
                                        'storage_conditions', 'net_weight', 'nutrition_info', 
                                        'image_url', 'status']:
                                if field not in row or not row[field]:
                                    if field == 'status':
                                        row[field] = 'active'  # 默认状态
                                    else:
                                        row[field] = ''  # 其他字段默认为空字符串
                                        
                            # 实际插入数据库
                            try:
                                sql = """
                                INSERT INTO products (
                                    name, supplier_id, code, category, shelf_life, 
                                    storage_conditions, net_weight, nutrition_info, image_url, status
                                ) VALUES (
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                                )
                                """
                                cursor.execute(sql, (
                                    row['name'], supplier_id, row['code'], row['category'],
                                    row['shelf_life'], row['storage_conditions'], 
                                    row['net_weight'], row['nutrition_info'], row['image_url'], 
                                    row['status']
                                ))
                                connection.commit()
                                imported += 1
                                
                                if imported <= 5 or imported % 50 == 0:
                                    print(f"已导入: 产品 {total}: 编码={row.get('code', 'NA')}, 供应商ID={supplier_id}, 名称={row.get('name', 'NA')}")
                            
                            except pymysql.err.IntegrityError as e:
                                if "Duplicate entry" in str(e):
                                    if total <= 5:
                                        print(f"跳过重复产品编码: {row['code']}")
                                else:
                                    raise
                            
                        else:
                            skipped += 1
                            if skipped <= 5:  # 只显示前5条跳过的记录
                                print(f"跳过：供应商ID {supplier_id} 不存在")
                    except (ValueError, KeyError) as e:
                        skipped += 1
                        if skipped <= 5:
                            print(f"跳过：无效的supplier_id - {row.get('supplier_id', 'NA')}")
                    
                    # 每50条显示一次进度
                    if total % 50 == 0:
                        print(f"处理进度：{total}条，成功：{imported}条，跳过：{skipped}条")
        except UnicodeDecodeError:
            print("GBK编码无法读取，尝试UTF-8编码")
            with open(csv_path, 'r', encoding='utf-8') as csvfile:
                reader = csv.DictReader(csvfile)
                # 与GBK编码处理逻辑相同，这里简化了
                print(f"UTF-8编码处理暂未实现")

        # 输出结果统计
        print("\n===== 导入结果统计 =====")
        print(f"总记录数: {total}")
        print(f"成功导入: {imported}")
        print(f"跳过记录数: {skipped}")
        
        # 验证数据库中的产品数量
        cursor.execute("SELECT COUNT(*) FROM products")
        product_count = cursor.fetchone()[0]
        print(f"数据库中的产品总数: {product_count}")

except Exception as e:
    print(f"发生错误: {e}")
    import traceback
    traceback.print_exc()
    connection.rollback()  # 发生错误时回滚事务
finally:
    connection.close()
    print("数据库连接已关闭")
    print("===== 处理完成 =====") 